package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.beans.PhotoInfos;
import cosplay.beans.Photos;
import cosplay.beans.Tags;

@Repository (value="photodao")
public class PhotoDAOImpl implements PhotoDAO{
	private JdbcTemplate jt;
	@Autowired
	public PhotoDAOImpl(DataSource data)
	{
		this.jt=new JdbcTemplate(data);
	}

	@Override
	public int add(final PhotoInfos photo) {
		// TODO Auto-generated method stub
		int id=0;
		if(photo!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_ADD(?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photo.getUserID());
							stm.setString(2, photo.getTitle());
							stm.setString(3, photo.getDescription());
							stm.registerOutParameter(4, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(4);
								obj[1]=rs;
								return obj;
							}});
			id=((Integer) obj[0]).intValue();
		}
		return id;
	}

	@Override
	public boolean delete(final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_DELETE(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=rs;
								return obj;
							}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@Override
	public boolean edit(final PhotoInfos photo, int id) {
		// TODO Auto-generated method stub
		if (id<0)
			return false;
		boolean sucess=false;
		if(photo!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_EDIT(?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photo.getUserID());
							stm.setString(2, photo.getTitle());
							stm.setString(3, photo.getDescription());
							stm.registerOutParameter(4, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(4);
								obj[1]=rs;
								return obj;
							}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@Override
	public PhotoInfos get(final int id) {
		// TODO Auto-generated method stub
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_GET(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=null;
								PhotoInfos photo =new PhotoInfos();
								photo.setTags(new ArrayList<Tags>());
								if (rs.next())
								{
									photo.setCompetitionID(rs.getInt("competitionid"));
									photo.setCompetitionName(rs.getString("competitionname"));
									photo.setDescription(rs.getString("description"));
									photo.setId(rs.getInt("id"));
									photo.setLastviewed(rs.getTimestamp("lastviewed"));
									photo.setPreviewid(rs.getInt("previewid"));
									photo.setSuggested(rs.getBoolean("suggested"));
									photo.setTitle(rs.getString("title"));
									photo.setUploaded(rs.getTimestamp("uploaded"));
									photo.setUserID(rs.getInt("userid"));
									photo.setUserName(rs.getString("username"));
									photo.setView(rs.getInt("viewed"));
									photo.setVoted(rs.getInt("voted"));
									Tags tag=new Tags();
									tag.setId(rs.getInt("tagid"));
									tag.setSystem(rs.getBoolean("systag"));
									tag.setWord(rs.getString("tag"));
									photo.getTags().add(tag);
								}
								while (rs.next())
								{
									Tags tag=new Tags();
									tag.setId(rs.getInt("tagid"));
									tag.setSystem(rs.getBoolean("systag"));
									tag.setWord(rs.getString("tag"));
									photo.getTags().add(tag);
								}
								obj[1]=photo;
								return obj;
							}});
			if(((Boolean) obj[0]).booleanValue())
				return (PhotoInfos) obj[1];
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PhotoInfos> getAll() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETALL(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<PhotoInfos> photos=new ArrayList<PhotoInfos>();
							while (rs.next())
							{
								PhotoInfos photo =new PhotoInfos();
								photo.setCompetitionID(rs.getInt("competitionid"));
								photo.setCompetitionName(rs.getString("competitionname"));
								photo.setDescription(rs.getString("description"));
								photo.setId(rs.getInt("id"));
								photo.setLastviewed(rs.getTimestamp("lastviewed"));
								photo.setPreviewid(rs.getInt("previewid"));
								photo.setSuggested(rs.getBoolean("suggested"));
								photo.setTitle(rs.getString("title"));
								photo.setUploaded(rs.getTimestamp("uploaded"));
								photo.setUserID(rs.getInt("userid"));
								photo.setUserName(rs.getString("username"));
								photo.setView(rs.getInt("viewed"));
								photo.setVoted(rs.getInt("voted"));
								photos.add(photo);
							}
							obj[1]=photos;
							return obj;
						}});
		return (List<PhotoInfos>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PhotoInfos> getByCompetition(final int competitionid) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETBYCOMPETITION(?,?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setInt(1, competitionid);
						stm.registerOutParameter(2, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(2);
							obj[1]=null;
							List<PhotoInfos> photos=new ArrayList<PhotoInfos>();
							while (rs.next())
							{
								PhotoInfos photo =new PhotoInfos();
								photo.setCompetitionID(rs.getInt("competitionid"));
								photo.setCompetitionName(rs.getString("competitionname"));
								photo.setDescription(rs.getString("description"));
								photo.setId(rs.getInt("id"));
								photo.setLastviewed(rs.getTimestamp("lastviewed"));
								photo.setPreviewid(rs.getInt("previewid"));
								photo.setSuggested(rs.getBoolean("suggested"));
								photo.setTitle(rs.getString("title"));
								photo.setUploaded(rs.getTimestamp("uploaded"));
								photo.setUserID(rs.getInt("userid"));
								photo.setUserName(rs.getString("username"));
								photo.setView(rs.getInt("viewed"));
								photo.setVoted(rs.getInt("voted"));
								photos.add(photo);
							}
							obj[1]=photos;
							return obj;
						}});
		return (List<PhotoInfos>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PhotoInfos> getByUpload(final Timestamp start, final Timestamp end) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETBYUPLOAD(?,?,?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setTimestamp(1, start);
						stm.setTimestamp(2, end);
						stm.registerOutParameter(3, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(3);
							obj[1]=null;
							List<PhotoInfos> photos=new ArrayList<PhotoInfos>();
							while (rs.next())
							{
								PhotoInfos photo =new PhotoInfos();
								photo.setCompetitionID(rs.getInt("competitionid"));
								photo.setCompetitionName(rs.getString("competitionname"));
								photo.setDescription(rs.getString("description"));
								photo.setId(rs.getInt("id"));
								photo.setLastviewed(rs.getTimestamp("lastviewed"));
								photo.setPreviewid(rs.getInt("previewid"));
								photo.setSuggested(rs.getBoolean("suggested"));
								photo.setTitle(rs.getString("title"));
								photo.setUploaded(rs.getTimestamp("uploaded"));
								photo.setUserID(rs.getInt("userid"));
								photo.setUserName(rs.getString("username"));
								photo.setView(rs.getInt("viewed"));
								photo.setVoted(rs.getInt("voted"));
								photos.add(photo);
							}
							obj[1]=photos;
							return obj;
						}});
		return (List<PhotoInfos>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PhotoInfos> getByUser(final int userid) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETBYUSER(?,?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setInt(1, userid);
						stm.registerOutParameter(2, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(2);
							obj[1]=null;
							List<PhotoInfos> photos=new ArrayList<PhotoInfos>();
							while (rs.next())
							{
								PhotoInfos photo =new PhotoInfos();
								photo.setCompetitionID(rs.getInt("competitionid"));
								photo.setCompetitionName(rs.getString("competitionname"));
								photo.setDescription(rs.getString("description"));
								photo.setId(rs.getInt("id"));
								photo.setLastviewed(rs.getTimestamp("lastviewed"));
								photo.setPreviewid(rs.getInt("previewid"));
								photo.setSuggested(rs.getBoolean("suggested"));
								photo.setTitle(rs.getString("title"));
								photo.setUploaded(rs.getTimestamp("uploaded"));
								photo.setUserID(rs.getInt("userid"));
								photo.setUserName(rs.getString("username"));
								photo.setView(rs.getInt("viewed"));
								photo.setVoted(rs.getInt("voted"));
								photos.add(photo);
							}
							obj[1]=photos;
							return obj;
						}});
		return (List<PhotoInfos>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PhotoInfos> getByTag(final int tagid) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETBYTAG(?,?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setInt(1, tagid);
						stm.registerOutParameter(2, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(2);
							obj[1]=null;
							List<PhotoInfos> photos=new ArrayList<PhotoInfos>();
							while (rs.next())
							{
								PhotoInfos photo =new PhotoInfos();
								photo.setCompetitionID(rs.getInt("competitionid"));
								photo.setCompetitionName(rs.getString("competitionname"));
								photo.setDescription(rs.getString("description"));
								photo.setId(rs.getInt("id"));
								photo.setLastviewed(rs.getTimestamp("lastviewed"));
								photo.setPreviewid(rs.getInt("previewid"));
								photo.setSuggested(rs.getBoolean("suggested"));
								photo.setTitle(rs.getString("title"));
								photo.setUploaded(rs.getTimestamp("uploaded"));
								photo.setUserID(rs.getInt("userid"));
								photo.setUserName(rs.getString("username"));
								photo.setView(rs.getInt("viewed"));
								photo.setVoted(rs.getInt("voted"));
								photos.add(photo);
							}
							obj[1]=photos;
							return obj;
						}});
		return (List<PhotoInfos>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PhotoInfos> getLastUploaded() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETLASTUPLOADED(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<PhotoInfos> photos=new ArrayList<PhotoInfos>();
							while (rs.next())
							{
								PhotoInfos photo =new PhotoInfos();
								photo.setCompetitionID(rs.getInt("competitionid"));
								photo.setCompetitionName(rs.getString("competitionname"));
								photo.setDescription(rs.getString("description"));
								photo.setId(rs.getInt("id"));
								photo.setLastviewed(rs.getTimestamp("lastviewed"));
								photo.setPreviewid(rs.getInt("previewid"));
								photo.setSuggested(rs.getBoolean("suggested"));
								photo.setTitle(rs.getString("title"));
								photo.setUploaded(rs.getTimestamp("uploaded"));
								photo.setUserID(rs.getInt("userid"));
								photo.setUserName(rs.getString("username"));
								photo.setView(rs.getInt("viewed"));
								photo.setVoted(rs.getInt("voted"));
								photos.add(photo);
							}
							obj[1]=photos;
							return obj;
						}});
		return (List<PhotoInfos>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PhotoInfos> getMostView() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETMOSTVIEW(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<PhotoInfos> photos=new ArrayList<PhotoInfos>();
							while (rs.next())
							{
								PhotoInfos photo =new PhotoInfos();
								photo.setCompetitionID(rs.getInt("competitionid"));
								photo.setCompetitionName(rs.getString("competitionname"));
								photo.setDescription(rs.getString("description"));
								photo.setId(rs.getInt("id"));
								photo.setLastviewed(rs.getTimestamp("lastviewed"));
								photo.setPreviewid(rs.getInt("previewid"));
								photo.setSuggested(rs.getBoolean("suggested"));
								photo.setTitle(rs.getString("title"));
								photo.setUploaded(rs.getTimestamp("uploaded"));
								photo.setUserID(rs.getInt("userid"));
								photo.setUserName(rs.getString("username"));
								photo.setView(rs.getInt("viewed"));
								photo.setVoted(rs.getInt("voted"));
								photos.add(photo);
							}
							obj[1]=photos;
							return obj;
						}});
		return (List<PhotoInfos>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PhotoInfos> getSuggested() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETSUGGESTED(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<PhotoInfos> photos=new ArrayList<PhotoInfos>();
							while (rs.next())
							{
								PhotoInfos photo =new PhotoInfos();
								photo.setCompetitionID(rs.getInt("competitionid"));
								photo.setCompetitionName(rs.getString("competitionname"));
								photo.setDescription(rs.getString("description"));
								photo.setId(rs.getInt("id"));
								photo.setLastviewed(rs.getTimestamp("lastviewed"));
								photo.setPreviewid(rs.getInt("previewid"));
								photo.setSuggested(rs.getBoolean("suggested"));
								photo.setTitle(rs.getString("title"));
								photo.setUploaded(rs.getTimestamp("uploaded"));
								photo.setUserID(rs.getInt("userid"));
								photo.setUserName(rs.getString("username"));
								photo.setView(rs.getInt("viewed"));
								photo.setVoted(rs.getInt("voted"));
								photos.add(photo);
							}
							obj[1]=photos;
							return obj;
						}});
		return (List<PhotoInfos>) obj[1];
	}

	@Override
	public boolean join(final int photoid) {
		// TODO Auto-generated method stub
		if(photoid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_JOIN(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photoid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public boolean quit(final int photoid) {
		// TODO Auto-generated method stub
		if(photoid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_QUIT(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photoid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public boolean suggest(final int photoid) {
		// TODO Auto-generated method stub
		if(photoid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_SUGGEST(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photoid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public boolean unsuggest(final int photoid) {
		// TODO Auto-generated method stub
		if(photoid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_UNSUGGEST(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photoid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public int addRaw( final Photos raw) {
		// TODO Auto-generated method stub
		int id=0;
		if(raw!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL RAW_ADD(?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, raw.getPhoto());
							stm.setBytes(2, raw.getPreview());
							stm.setBytes(3, raw.getImage());
							stm.setBoolean(4, raw.isPhotopreview());
							stm.registerOutParameter(5, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(5);
							obj[1]=rs;
							return obj;
						}});
			id=((Integer) obj[0]).intValue();
		}
		return id;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getRawByPhoto(final int photoid) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL RAW_GETBYPHOTO(?,?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setInt(1, photoid);
						stm.registerOutParameter(2, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(2);
							obj[1]=null;
							List<Integer> previewids=new ArrayList<Integer>();
							while (rs.next())
							{
								previewids.add(Integer.valueOf(rs.getInt("id")));
							}
							obj[1]=previewids;
							return obj;
						}});
		return (List<Integer>) obj[1];
	}

	@Override
	public byte[] getImage(final int rawid) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL RAW_GETIMAGE(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setInt(1, rawid);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							if (rs.next())
							{
								obj[1]=rs.getBytes("image");
							}
							return obj;
						}});
		return (byte[]) obj[1];
	}

	@Override
	public byte[] getPreview(final int rawid) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL RAW_GETPREVIEW(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setInt(1, rawid);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							if (rs.next())
							{
								obj[1]=rs.getBytes("previewimage");
							}
							return obj;
						}});
		return (byte[]) obj[1];
	}

	@Override
	public boolean moveRaw(final int rawid, final int newphotoid) {
		// TODO Auto-generated method stub
		if(newphotoid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL RAW_MOVE(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, rawid);
							stm.setInt(2, newphotoid);
							stm.registerOutParameter(3, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(3);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public boolean setPreview(final int rawid) {
		// TODO Auto-generated method stub
		if(rawid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL RAW_SETPREVIEW(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, rawid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(2);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getAllId() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETALLID(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<Integer> photoids=new ArrayList<Integer>();
							while (rs.next())
							{
								photoids.add(Integer.valueOf(rs.getInt("id")));
							}
							obj[1]=photoids;
							return obj;
						}});
		return (List<Integer>) obj[1];
	}

}
